﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace paper.evisa99.com.Controllers.Class
{
    public static class DB
    {
        public static string Ver = "1.2s";
        public static SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AdoConnection"].ToString() + "Password=Liu212121;");
        public static string SqlStr;
        ///====================================================================
        /// <summary>
        /// 执行SQL语句，返回唯一字符串结果
        /// </summary>
        public static string ExecSql2Scalar()
        {
            string returnValue;
            SqlDataAdapter rsAdapter = new SqlDataAdapter(SqlStr, Conn);
            DataTable tmpDataTable = new DataTable();
            rsAdapter.Fill(tmpDataTable);
            if (tmpDataTable.Rows.Count > 0) { returnValue = tmpDataTable.Rows[0][0].ToString(); } else { returnValue = ""; }
            return returnValue;
        }
        ///--------------------------------------------------------------------------------------------
        /// <summary>
        /// 执行SQL语句，返回数据总行数
        /// </summary>
        public static int ExecSql2Count()
        {
            SqlDataAdapter rsAdapter = new SqlDataAdapter(SqlStr, Conn);
            DataTable tmpDataTable = new DataTable();
            rsAdapter.Fill(tmpDataTable);
            return tmpDataTable.Rows.Count;
        }
        ///====================================================================
        /// <summary>
        /// 执行SQL语句，不返回执行结果，支持AutoClose
        /// </summary>
        public static void ExecSqlNonQuery()
        {
            bool IsAutoClose = false;
            if (Conn.State == System.Data.ConnectionState.Closed)
            {
                Conn.Open();
                IsAutoClose = true;
            }
            SqlCommand Comm = new SqlCommand(SqlStr, Conn);
            Comm.ExecuteNonQuery();
            if (IsAutoClose) { Conn.Close(); }
        }
        ///====================================================================
        //定义SQL值参临时数据表
        public static DataTable Param;
        //构建临时数据表
        public static void InitParam()
        {
            Param = new DataTable();
            Param.Columns.Add("Text");
            Param.Columns.Add("Value");
        }
        //向临时数据表插入一个参数
        public static void InstParam(string Text, string Value)
        {
            DataRow ParamItem;
            ParamItem = Param.NewRow();
            ParamItem[0] = Text;
            ParamItem[1] = Value;
            Param.Rows.Add(ParamItem);
        }
        //释放临时参数表
        public static void DropParam()
        {
            Param = null;
        }
        ///====================================================================
        /// <summary>
        /// 执行SQL语句，向SQL语句传递临时数据表中的参数，不返回结果，支持AutoClose
        /// </summary>
        public static void ExecSqlWithParam()
        {
            bool IsAutoClose = false;
            if (Conn.State == System.Data.ConnectionState.Closed)
            {
                Conn.Open();
                IsAutoClose = true;
            }
            SqlCommand Comm = new SqlCommand(SqlStr, Conn);
            for (var i = 0; i <= Param.Rows.Count - 1; i++)
            {
                Comm.Parameters.AddWithValue(Param.Rows[i][0].ToString(), Param.Rows[i][1].ToString());
            }
            Comm.ExecuteNonQuery();
            if (IsAutoClose) { Conn.Close(); }
        }
        ///--------------------------------------------------------------------------------------------
        /// <summary>
        /// 执行SQL语句，返回DataTable格式结果，支持AutoClose
        /// </summary>
        public static DataTable ExecSql2Table()
        {
            SqlDataAdapter rsAdapter = new SqlDataAdapter(SqlStr, Conn);
            DataTable tmpDataTable = new DataTable();
            rsAdapter.Fill(tmpDataTable);
            return tmpDataTable;
        }
        ///--------------------------------------------------------------------------------------------
        /// <summary>
        /// 执行SQL语句，向SQL语句传递临时数据表中的参数，返回DataTable格式结果，支持AutoClose
        /// </summary>
        public static DataTable SelectSqlWithParam()
        {
            SqlDataAdapter rsAdapter = new SqlDataAdapter(SqlStr, Conn);
            DataTable tmpDataTable = new DataTable();

            for (var i = 0; i <= Param.Rows.Count - 1; i++)
            {
                rsAdapter.SelectCommand.Parameters.AddWithValue(Param.Rows[i][0].ToString(), Param.Rows[i][1].ToString());
            }

            rsAdapter.Fill(tmpDataTable);
            return tmpDataTable;
        }
        ///--------------------------------------------------------------------------------------------
        /// <summary>
        /// 执行存贮过程，向SQL语句传递临时数据表中的参数，返回第一行第一列字符串，支持AutoClose
        /// </summary>
        public static string ExecSpWithParam()
        {
            bool IsAutoClose = false;
            if (Conn.State == System.Data.ConnectionState.Closed)
            {
                Conn.Open();
                IsAutoClose = true;
            }
            SqlCommand Comm = new SqlCommand(SqlStr, Conn);
            Comm.CommandType = CommandType.StoredProcedure;
            string result;
            for (var i = 0; i <= Param.Rows.Count - 1; i++)
            {
                Comm.Parameters.AddWithValue(Param.Rows[i][0].ToString(), Param.Rows[i][1].ToString());
            }
            result = Comm.ExecuteScalar().ToString();
            if (IsAutoClose) { Conn.Close(); }
            return result;
        }
        ///====================================================================
    }
}